Cost Center manager details from an employee #

If you have an employee and you need to know his cost center manager:

SELECT
        peo.person_number employee_number,
        per_name.display_name            ,
        segs.*                           ,
        cs_manager.*
FROM
        per_all_people_f peo                     ,
        per_persons per                          ,
        per_person_names_f per_name              ,
        per_all_assignments_m asg                ,
        per_assignment_status_types_tl asg_status,
        (
                select
                        cc.CODE_COMBINATION_ID ,
                        cc.segment2 cost_center,
                        cc.segment1 company    ,
                        (
                                SELECT
                                        --segvalsets.value_set_code             ,
                                        segvals.description                      
                                FROM
                                        fusion.fnd_vs_values_vl segvals,
                                        fusion.fnd_vs_value_sets segvalsets
                                WHERE
                                        segvals.value_set_id    = segvalsets.value_Set_id
                                and     segvalsets.value_Set_id = '61003'
                                and     value                   = cc.segment2 ) cost_center_desc,
                        (
                                SELECT
                                        --segvalsets.value_set_code             ,
                                        segvals.description                      
                                FROM
                                        fusion.fnd_vs_values_vl segvals,
                                        fusion.fnd_vs_value_sets segvalsets
                                WHERE
                                        segvals.value_set_id    = segvalsets.value_Set_id
                                and     segvalsets.value_Set_id = '61002'
                                and     value                   = cc.segment1 ) company_desc
                from
                        GL_CODE_COMBINATIONS cc) segs,
        (
                select distinct
                        per.DISPLAY_NAME mgr_display_name,
                        per.person_id mgr_person_id      ,
                        cs.ORG_INFORMATION3 company      ,
                        ORG_INFORMATION1 cost_center
                from
                        fusion.per_person_names_F per,
                        fusion.HR_ORGANIZATION_INFORMATION cs
                where
                        per.person_id              = cs.ORG_INFORMATION6
                and     cs.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO') cs_manager
WHERE
        per.person_id                 = peo.person_id
AND     per_name.person_id            = peo.person_id
AND     asg.person_id                 = peo.person_id
AND     per_name.legislation_code     = asg.legislation_code
AND     asg.assignment_status_type_id = asg_status.assignment_status_type_id
AND     per_name.name_type            ='GLOBAL'
AND     asg.primary_flag              = 'Y'
AND     asg.system_person_type        = 'EMP'
AND     asg.assignment_status_type    = 'ACTIVE'
and     sysdate between asg.effective_start_date and asg.effective_end_date
and     sysdate between peo.effective_start_date and peo.effective_end_date
and     asg_status.language       = 'US'
and     cs_manager.cost_center(   +) =  segs.cost_center
and     cs_manager.company(       +) =  segs.company
and     segs.CODE_COMBINATION_ID (+) = asg.default_code_comb_id
AND     segs.cost_center             <> '0'

Cost Center manager from code combination #

SELECT
        peo.person_number employee_number  ,
        per_name.display_name Manager_name ,
        segs.CODE_COMBINATION_ID           ,
        segs.cost_center                   ,
        cs_manager.mgr_person_id
FROM
        per_all_people_f peo       ,
        per_persons per            ,
        per_person_names_f per_name,
        (
                select
                        cc.CODE_COMBINATION_ID ,
                        cc.segment2 cost_center,
                        cc.segment1 company
                from
                        GL_CODE_COMBINATIONS cc) segs,
        (
                select distinct
                        cs.ORG_INFORMATION6 mgr_person_id ,
                        cs.ORG_INFORMATION3 company       ,
                        ORG_INFORMATION1 cost_center
                from
                        fusion.HR_ORGANIZATION_INFORMATION cs
                where
                        cs.ORG_INFORMATION_CONTEXT = 'PER_GL_COST_CENTER_INFO') cs_manager
WHERE
        per.person_id      = peo.person_id
and     per.person_id      = cs_manager.mgr_person_id
AND     per_name.person_id = peo.person_id
AND     per_name.name_type ='GLOBAL'
and     sysdate between peo.effective_start_date and peo.effective_end_date
and     cs_manager.cost_center = segs.cost_center
and     cs_manager.company     = segs.company
AND     segs.cost_center       <> '0'